package com.controller;

import com.beans.StudentInformationEntity;
import com.common.entity.R;
import com.common.utils.CalendarFormat;
import com.repository.StudentInformationRepoitory;
import org.apache.poi.ss.usermodel.*;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.servlet.http.HttpServletRequest;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.*;

/**
 * 学生信息管理
 * @author wyj
 */
@Transactional(rollbackFor = Exception.class)
@RestController
@RequestMapping("studentInformation")
public class StudentInformationController {

    @Resource
    private EntityManager entityManager;

    @Resource
    private StudentInformationRepoitory studentInformationRepoitory;

    /**
     * 修改或保存
     * @param studentInformationEntity 如果id数据库有则为修改，没有为新增
     * @return
     */
    @PostMapping("mergeStudentInformation")
    public R mergeStudentInformation(StudentInformationEntity studentInformationEntity){
        studentInformationEntity.setCreateTime(new Date());
        studentInformationEntity.setLastloginTime(new Date());
        StudentInformationEntity cqe = entityManager.merge(studentInformationEntity);
        return R.ok(cqe);
    }

    /**
     * 删除
     * @param id
     * @return
     */
    @GetMapping("delStudentInformation")
    public R delStudentInformation(Long id){
        studentInformationRepoitory.deleteById(id);
        return R.ok();
    }

    /**
     * 导入数据
     * @param request
     * @param studentInformationEntity
     * @param fileExcelTem
     * @return
     * @throws IOException
     */
    @PostMapping("mergeStudentInformationExcel")
    public R mergeStudentInformationExcel(HttpServletRequest request, StudentInformationEntity studentInformationEntity, MultipartFile fileExcelTem) throws IOException {
        studentInformationEntity.setCreateTime(new Date());
        studentInformationEntity.setLastloginTime(new Date());

        InputStream ins = fileExcelTem.getInputStream();

        Map<String,List<Map<String,Object>>> map = officeExcelRead(ins);
        StringBuilder stringBuilder = listToSql(map,studentInformationEntity);

        int num = entityManager.createNativeQuery(stringBuilder+"").executeUpdate();
        return R.ok(num);
    }

    /**
     * 分页获取详情，可根据需求传入不同数量的参数
     *
     * 需要分页的话 page rows 必须都传入
     * 需要排序的话，请至少传入排序字段，默认升序
     *
     * @param state  状态：1正常，2休学，3当兵，4退学，5停考
     * @param pid  专业班级
     * @param page 页数
     * @param rows 笔数
     * @param sort 排序字段
     * @param order 'asc' or 'desc'.
     * @author wyj
     * @date 2021/4/21
     * @return
     */
    @GetMapping("queryStudentInformationPid")
    public R queryStudentInformationPid (Integer state, Long pid, Integer page, Integer rows, String sort, String order){
        //region 组装hql
        StringBuilder sql = new StringBuilder("select * from student_information where 1 = 1 ");
        StringBuilder countSql = new StringBuilder("select count(id) from student_information where 1 = 1 ");
        if (state != null) {
            sql.append(" and state = :state ");
            countSql.append(" and state = :state ");
        }

        if (pid != null) {
            sql.append(" and pid = :pid ");
            countSql.append(" and pid = :pid ");
        }

        if (!StringUtils.hasLength(sort)) {
            sql.append(" order by ").append(sort).append(" ");
            if (!StringUtils.hasLength(order)) {
                sql.append(order);
            }
        }

        if (page != null && rows != null) {
            sql.append(" limit :offset , :rows ");
        }
        // 组装hql
        // 组装查询对象和参数
        Query query = entityManager.createNativeQuery(sql.toString(), StudentInformationEntity.class);
        Query queryCount = entityManager.createNativeQuery(countSql.toString());
        if (state != null) {
            query.setParameter("state", state);
            queryCount.setParameter("state", state);
        }

        if (pid != null) {
            query.setParameter("pid", pid);
            queryCount.setParameter("pid", pid);
        }

        if (page != null && rows != null) {
            query.setParameter("offset", (page - 1) * rows).setParameter("rows", rows);
        }

        List<StudentInformationEntity> resultList = query.getResultList();
        Object count = queryCount.getSingleResult();
        R ok = R.ok();
        ok.put("total", count);
        ok.put("rows", resultList);
        return ok;
    }

    /**
     * <p>office-excel的读取</p>
     * @param ins
     * @return
     */
    public Map<String,List<Map<String,Object>>> officeExcelRead(InputStream ins){
        Map<String,List<Map<String,Object>>> mapSheets = new HashMap<>();
        try (
                Workbook wb = WorkbookFactory.create(ins);
        ) {
            //得到sheet的数量
            int sheetNum = wb.getNumberOfSheets();
            for (int i = 0; i < sheetNum; i++) {
                String sheetName =  wb.getSheetName(i);
                List<Map<String,Object>> list = new ArrayList<>();
                //wb.getSheetAt(i);
                Sheet sheet = wb.getSheet(sheetName);
                //得到当前sheet的行数
                int sheetRows = sheet.getPhysicalNumberOfRows();
                //System.out.println(sheetRows);
                for (int j = 1; j < sheetRows ; j++) {
                    Row row = sheet.getRow(j);
                    Map<String,Object> map = new LinkedHashMap<>();

                    //使用DecimalFormat类对科学计数法格式的数字进行格式化
                    DecimalFormat df = new DecimalFormat("0");
                    String id = df.format(row.getCell(0).getNumericCellValue());

                    map.put("id",id);
                    map.put("name",row.getCell(1));
                    Cell ageCell = row.getCell(2);
                    int sex = 2;
                    if(ageCell.getStringCellValue().equalsIgnoreCase("男")){
                        sex = 1;
                    }
                    map.put("sex",sex);
                    map.put("sfz",row.getCell(3));
                    list.add(map);
                }
                mapSheets.put(sheetName,list);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return mapSheets;
    }

    /**
     * 生成inser …… values(?,?,...),(?,?,...),……语句
     * @param map
     * @param studentInformationEntity
     * @return
     */
    public StringBuilder listToSql(Map<String,List<Map<String,Object>>> map, StudentInformationEntity studentInformationEntity){
        studentInformationEntity.setStatus(1);
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("insert into student_information");
        String cal = CalendarFormat.getYYMMDDTime();
        boolean b = true;
        for (String obj : map.keySet()) {
            List<Map<String, Object>> list = map.get(obj);
            for (Map<String, Object> mapTem : list) {
                String cellNames = "";
                String values = "";
                String kaohao = "";
                for (String key : mapTem.keySet()) {
                    cellNames += key + ",";
                    values += "'" + mapTem.get(key) + "',";
                    if(key.equalsIgnoreCase("id")){
                        kaohao = mapTem.get(key)+"";
                    }
                }
                if(b){
                    cellNames = cellNames.substring(0, cellNames.length() - 1);
                    stringBuilder.append("(");
                    stringBuilder.append(cellNames);
                    stringBuilder.append(",pid,kaohao,createTime,lastLoginTime,status) values");
                    b=false;
                }

                stringBuilder.append("(");
                stringBuilder.append(values);
                stringBuilder.append("'"+studentInformationEntity.getPid()+"',");
                stringBuilder.append("'"+kaohao+"',");
                stringBuilder.append("'"+ cal +"',");
                stringBuilder.append("'"+ cal +"',");
                stringBuilder.append("'"+studentInformationEntity.getStatus()+"'");
                stringBuilder.append("),");
            }
        }
        stringBuilder.deleteCharAt(stringBuilder.length()-1);
        stringBuilder.append(";");
        return stringBuilder;
    }
}
